﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace Donate.DAl
{
   public class DonHistoryEntity
    {
       public Int32 Id { set; get; }
       public DateTime DateOfDonate {set;get;}
       public Int32 DonId {set;get;}
       public string DonTo {set;get;}
       public string Notes { set; get; }
     }

   public class DonHistory
   {
       //private static string connstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='F:\بنك الدم\Donate\Donate\bin\Debug\Donates.accdb' ;Jet OLEDB:Database password =Yarabe11 ";
       private static string connstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + (System.Reflection.Assembly.GetExecutingAssembly().Location).Remove((System.Reflection.Assembly.GetExecutingAssembly().Location).IndexOf("Donate.exe")) + "Donates.accdb;Jet OLEDB:Database Password=Yarabe11";
       private static string cmdtext = "";
       //*************************************
       public static bool Add(DonHistoryEntity t)
       {
           bool flag = true;
           cmdtext = "INSERT INTO DonHistory (DateOfDonate,DonId,DonTo,Notes) values (?,?,?,?)";
           OleDbConnection con = new OleDbConnection(connstring);
           OleDbCommand cmd = new OleDbCommand(cmdtext, con);

           cmd.Parameters.AddWithValue(@"DateOfDonate", t.DateOfDonate);
           cmd.Parameters.AddWithValue(@"DonId", t.DonId);
           cmd.Parameters.AddWithValue(@"DonTo", t.DonTo);
           cmd.Parameters.AddWithValue(@"Notes", t.Notes);

           try
           {
               con.Open();
               int x = cmd.ExecuteNonQuery();
               if (x == 0)
               {
                   flag = false;
               }
           }
           catch (Exception e)
           {
               MessageBox.Show("Error In Add" + e.Message);
               flag = false;

           }
           finally
           {
               if (con.State == ConnectionState.Open)
               {
                   con.Close();
               }
           }
           return flag;
       }
       public static bool Update(DonHistoryEntity t)
       {

           bool flag = true;
           cmdtext = "Update DonHistory set DateOfDonate= @DateOfDonate , DonId = @DonId , DonTo = @DonTo, Notes = @Notes Where Id = @Id";

           OleDbConnection con = new OleDbConnection(connstring);
           OleDbCommand cmd = new OleDbCommand(cmdtext, con);

           cmd.Parameters.AddWithValue(@"DateOfDonate", t.DateOfDonate);
           cmd.Parameters.AddWithValue(@"DonId", t.DonId);
           cmd.Parameters.AddWithValue(@"DonTo", t.DonTo);
           cmd.Parameters.AddWithValue(@"Notes", t.Notes);
           cmd.Parameters.AddWithValue(@"Id", t.Id);
           try
           {
               con.Open();

               int x = cmd.ExecuteNonQuery();
               if (x == 0)
               {
                   flag = false;
               }
           }
           catch (Exception e)
           {
               MessageBox.Show("Error In Update" + e.Message);
               flag = false;

           }
           finally
           {
               if (con.State == ConnectionState.Open)
               {
                   con.Close();
               }

           }
           return flag;
       }
       public static bool Delete(int e)
       {
           bool flag = true;
           cmdtext = "delete from DonHistory where Id = @Id ";

           OleDbConnection con = new OleDbConnection(connstring);
           OleDbCommand cmd = new OleDbCommand(cmdtext, con);

           cmd.Parameters.AddWithValue(@"Id", e);


           try
           {
               con.Open();

               int x = cmd.ExecuteNonQuery();
               if (x == 0)
               {
                   flag = false;
               }
           }
           catch (Exception w)
           {
               MessageBox.Show("Error In Delete" + w.Message);
           }
           finally
           {
               if (con.State == ConnectionState.Open)
               {
                   con.Close();
               }
           }
           return flag;




       }
       public static DonHistoryEntity GetById(int x)
       {
           DonHistoryEntity d = null;
           cmdtext = "Select * from DonHistory Where Id=?";

           OleDbConnection con = new OleDbConnection(connstring);
           OleDbCommand cmd = new OleDbCommand(cmdtext, con);

           cmd.Parameters.AddWithValue(@"Id", x);

           try
           {
               con.Open();

               OleDbDataReader reader = cmd.ExecuteReader();
               if (reader.Read())
               {
                   d = FillEntityFromReader(reader);
                   reader.Close();
               }
           }
           catch (Exception e)
           {
               MessageBox.Show("Error In Select" + e.Message);
           }
           finally
           {
               if (con.State == ConnectionState.Open)
               {
                   con.Close();
               }
           }
           return d;

       }


       ////GetHistoryByName
       public static List<DonHistoryEntity> GetHistoryByName(string name)
       {
           List<DonHistoryEntity> ListEntity = new List<DonHistoryEntity>();
           DonHistoryEntity d = null;
           cmdtext = "SELECT DonHistory.DateOfDonate, DonHistory.DonTo, DonHistory.Notes, DonHistory.Id FROM Donates , DonHistory Where Donates.Id = DonHistory.DonId and Nameof=?";


           OleDbConnection con = new OleDbConnection(connstring);
           OleDbCommand cmd = new OleDbCommand(cmdtext, con);
           cmd.Parameters.AddWithValue(@"Nameof", name);
           try
           {
               con.Open();

               OleDbDataReader reader = cmd.ExecuteReader();
               while (reader.Read())
               {
                   d = FillEntityFromReader(reader);
                   ListEntity.Add(d);
               }
               reader.Close();
           }

           catch (Exception e)
           {
               MessageBox.Show("Error In Select" + e.Message);
           }
           finally
           {
               if (con.State == ConnectionState.Open) ;
               con.Close();
           }
           return ListEntity;

       }
       public static List<DonHistoryEntity> GetAllByDonId()
       {
           List<DonHistoryEntity> ListEntity = new List<DonHistoryEntity>();
           DonHistoryEntity d = null;
           cmdtext = "SELECT DonHistory.DateOfDonate, DonHistory.DonTo, DonHistory.Notes, DonHistory.Id FROM Donates , DonHistory Where Donates.Id = DonHistory.DonId";
          

           OleDbConnection con = new OleDbConnection(connstring);
           OleDbCommand cmd = new OleDbCommand(cmdtext, con);

           try
           {
               con.Open();

               OleDbDataReader reader = cmd.ExecuteReader();
               while (reader.Read())
               {
                   d = FillEntityFromReader(reader);
                   ListEntity.Add(d);
               }
               reader.Close();
           }

           catch (Exception e)
           {
               MessageBox.Show("Error In Select" + e.Message);
           }
           finally
           {
               if (con.State == ConnectionState.Open) ;
               con.Close();
           }
           return ListEntity;

       }
      // *****************************************
       public static DonHistoryEntity FillEntityFromReader(OleDbDataReader reader)
       {
           DonHistoryEntity d = new DonHistoryEntity();
           d.Id=Int32.Parse( reader["Id"].ToString());
           if (reader["DateOfDonate"].ToString()!=null) 
           d.DateOfDonate =Convert.ToDateTime( reader["DateOfDonate"].ToString());

        //   ************* فيه هنا مشكله **********
           //if (reader["DonId"].ToString() != null)
           //    d.DonId = Int32.Parse(reader["DonId"].ToString());

           if (reader["DonTo"].ToString()!=null)
           d.DonTo=reader["DonTo"].ToString();
           if (reader["Notes"].ToString()!=null)
           d.Notes=reader["Notes"].ToString();
           return d;
       }
     
   }
   }

